package ae.pcfc.cldos.online.common.clddb.dao.provider;

import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import ae.pcfc.cldos.online.common.clddb.dao.AppMetaDataDAO;
import ae.pcfc.cldos.online.common.clddb.domain.AppMetaData;
import ae.pcfc.cldos.online.common.clddb.domain.ApplicationStatus;
import ae.pcfc.cldos.online.common.clddb.domain.EmployeeDetail;
import ae.pcfc.cldos.online.common.clddb.domain.EmploymentVisaCancel;
import ae.pcfc.cldos.online.common.clddb.domain.EmploymentVisaRenew;
import ae.pcfc.cldos.online.common.clddb.domain.NocLetter;
import ae.pcfc.cldos.online.common.clddb.domain.PassportDetail;
import ae.pcfc.cldos.online.common.clddb.domain.Process;
import ae.pcfc.cldos.online.common.clddb.domain.ReviewComments;
import ae.pcfc.cldos.online.common.clddb.domain.VisitVisaCancel;
import ae.pcfc.cldos.online.common.dao.GenericDAO;
import ae.pcfc.cldos.online.common.util.UtilCommon;
import ae.pcfc.cldos.online.common.vo.UserInformationVO;
import ae.pcfc.cldos.online.common.vo.tasks.TaskRequestDbRestrictions;

/**
 * 
 * @author arosa
 *
 */
@Repository
public class AppMetaDataDAOImpl extends GenericDAO implements AppMetaDataDAO{
	
	private static final String __EMPLOYE_DETAIL_ALIAS = "employeDetailAlias";
	//private static final String __TEMP_FILE_STORAGE_ALIAS = "tempFileStorageAlias";
	
	public static final String __ADDRESS_DETAILS_ASSOTH_PATH = __EMPLOYE_DETAIL_ALIAS+"."+EmployeeDetail.__ADDRESS_DETAILS;
	public static final String __PASSPORT_DETAILS_ASSOTH_PATH = __EMPLOYE_DETAIL_ALIAS+"."+EmployeeDetail.__PASSPORT_DETAILS;
	public static final String __PERSONAL_DETAILS_ASSOTH_PATH = __EMPLOYE_DETAIL_ALIAS+"."+EmployeeDetail.__PERSONAL_DETAILS;
	
	private static final String __ADDRESS_DETAIL_ALIAS = "addressDetailAlias";
	private static final String __PASSPORT_DETAIL_ALIAS = "passportDetailAlias";
	private static final String __PERSONAL_DETAIL_ALIAS = "personalDetailAlias";
	private static final String __SERVICE_MASTER_ALIAS = "serviceMasterAlias";
	private static final String __APPLICATION_STATUS_ALIAS = "appStatusAlias";
	
	private static final String __PASSPORT_DETAIL_ALIAS_PASSPORT_NAME = __PASSPORT_DETAIL_ALIAS+"."+PassportDetail.PASSPORT_NAME_EN;
	private static final String __PASSPORT_DETAIL_ALIAS_PASSPORT_NO = __PASSPORT_DETAIL_ALIAS+"."+PassportDetail.PASSPORT_NO;
	private static final String __EMPLOYEE_DETAIL_ALIAS_PASSPORT_NAME = __EMPLOYE_DETAIL_ALIAS+"."+EmployeeDetail.__PASSPORT_NAME;
	
	private static final String __EMPLOYMENT_VISA_RENEWAL_DETAILS_ALIAS = "empVisaRenewalAlias";
	private static final String __EMPLOYMENT_VISA_CANCELATION_DETAILS_ALIAS = "empVisaCancelationAlias";
	private static final String __VISIT_VISA_CANCELATION_DETAILS_ALIAS = "visVisaCancelationAlias";
	private static final String __VISIT_VISA_DETAILS_ALIAS = "visVisaAlias";
	private static final String __NOC_LETTER_ALIAS = "nocLetterAlias";
	
	private static final String __EMPLOYMENT_VISA_RENEWAL_DETAILS_EMP_ID = 
			__EMPLOYMENT_VISA_RENEWAL_DETAILS_ALIAS+"."+EmploymentVisaRenew.EMPLOYEE_ID;
	
	private static final String __EMPLOYMENT_VISA_CANCELATION_DETAILS_EMP_ID = 
			__EMPLOYMENT_VISA_CANCELATION_DETAILS_ALIAS+"."+EmploymentVisaCancel.EMPLOYEE_ID;
	
	private static final String __VISIT_VISA_CANCELATION_DETAILS_EMP_ID = 
			__VISIT_VISA_CANCELATION_DETAILS_ALIAS+"."+VisitVisaCancel.EMPLOYEE_ID;
	
	private static final String __NOC_LETTER_EMP_ID = __NOC_LETTER_ALIAS+"."+NocLetter.EMPLOYEE_ID;
	
	public void add(AppMetaData appMetaData){
		saveOrUpdate(appMetaData);
	}
	
	public void update(AppMetaData appMetaData){
		saveOrUpdate(appMetaData);
	}
	
	@SuppressWarnings("unchecked")
	public AppMetaData getAppMetaDataById(Long appId, String processCode){
		
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.add(Restrictions.idEq(appId));
		
		if(processCode.equalsIgnoreCase(Process.PROCESS_EMPLOYMENT_VISA)){
			joinEmploymentVisaInfo(criteria);
		} else if(processCode.equalsIgnoreCase(Process.PROCESS_EMPLOYMENT_VISA_CANCELATION)) {
			joinEmploymentVisaCancelationInfo(criteria);
		} else if(processCode.equalsIgnoreCase(Process.PROCESS_EMPLOYMENT_VISA_RENEWAL)){
			joinEmploymentVisaRenewalInfo(criteria); 
		} else if(processCode.equalsIgnoreCase(Process.PROCESS_CANCEL_VISIT_VISA)){
			joinVisitVisaCancelInfo(criteria);
		} else if(processCode.equalsIgnoreCase(Process.PROCESS_NOC_LETTERS)){
			joinNocLettersInfo(criteria);
		}else if(processCode.equalsIgnoreCase(Process.PROCESS_VISIT_VISA)){
			joinVisitVisaInfo(criteria);
		}
		

		
		criteria.setFetchMode(AppMetaData.__TEMP_FILE_STORAGES, FetchMode.JOIN);
		
		criteria.createAlias(AppMetaData.__SERVICE_MASTER, __SERVICE_MASTER_ALIAS, Criteria.INNER_JOIN);
		criteria.setFetchMode(__SERVICE_MASTER_ALIAS, FetchMode.JOIN);
		
		criteria.createAlias(AppMetaData.__APPLICATION_STATUS, __APPLICATION_STATUS_ALIAS, Criteria.INNER_JOIN);
		criteria.setFetchMode(__APPLICATION_STATUS_ALIAS, FetchMode.JOIN);
		
		AppMetaData appMetaData = findByCriteria(criteria);
		
		DetachedCriteria commentsCriteria = DetachedCriteria.forClass(ReviewComments.class);
		commentsCriteria.setFetchMode(ReviewComments.__APPLICATION_STATUS, FetchMode.JOIN);
		commentsCriteria.add(Restrictions.eq(ReviewComments.__APP_ID, appMetaData.getApplicationId()));
		commentsCriteria.addOrder(Order.desc(ReviewComments.__DATE));
		appMetaData.setReviewComments((List<ReviewComments>)getListByCriteria(commentsCriteria));
		
		return appMetaData;
	}
	
	private void joinEmploymentVisaInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__EMPLOYEE_DETAILS, __EMPLOYE_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__EMPLOYE_DETAIL_ALIAS, FetchMode.JOIN);
		criteria.createAlias(__ADDRESS_DETAILS_ASSOTH_PATH, __ADDRESS_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__ADDRESS_DETAIL_ALIAS, FetchMode.JOIN);
		criteria.createAlias(__PASSPORT_DETAILS_ASSOTH_PATH, __PASSPORT_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__PASSPORT_DETAIL_ALIAS, FetchMode.JOIN);
		criteria.createAlias(__PERSONAL_DETAILS_ASSOTH_PATH, __PERSONAL_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__PERSONAL_DETAIL_ALIAS, FetchMode.JOIN);
	}
	
	private void joinEmploymentVisaCancelationInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__EMPLOYMENT_VISA_CANCEL, 
				__EMPLOYMENT_VISA_CANCELATION_DETAILS_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__EMPLOYMENT_VISA_CANCELATION_DETAILS_ALIAS, FetchMode.JOIN);
	}
	
	private void joinEmploymentVisaRenewalInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__EMPLOYMENT_VISA_RENEWAL, 
				__EMPLOYMENT_VISA_RENEWAL_DETAILS_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__EMPLOYMENT_VISA_RENEWAL_DETAILS_ALIAS, FetchMode.JOIN);
	}
	
	private void joinVisitVisaCancelInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__VISIT_VISA_CANCEL, 
				__VISIT_VISA_CANCELATION_DETAILS_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__VISIT_VISA_CANCELATION_DETAILS_ALIAS, FetchMode.JOIN);
	}
	
	private void joinNocLettersInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__NOC_LETTERS, 
				__NOC_LETTER_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__NOC_LETTER_ALIAS, FetchMode.JOIN);
	}
	
	private void joinVisitVisaInfo(DetachedCriteria criteria){
		criteria.createAlias(AppMetaData.__VISIT_VISA, 
				__VISIT_VISA_DETAILS_ALIAS, Criteria.INNER_JOIN);
		criteria.setFetchMode(__VISIT_VISA_DETAILS_ALIAS, FetchMode.JOIN);
	}
	
	@Transactional(propagation=Propagation.REQUIRES_NEW, readOnly=true)
	public AppMetaData getPlainAppMetaDataById(Long appId){
		return findById(AppMetaData.class, appId);
	}
	
	/**
	 * @param appId
	 * @return
	 */
	public AppMetaData findAppMetaDataById(Long appId){
		return findById(AppMetaData.class, appId);
	}
	/**
	 * @param userInformationVO
	 * @param taskRequestDbRestrictions
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AppMetaData> getAllAppRequests(UserInformationVO userInformationVO, TaskRequestDbRestrictions taskRequestDbRestrictions){
		DetachedCriteria criteria = getAllAppRequestsCriteria(userInformationVO, taskRequestDbRestrictions);
		Integer currentPage = taskRequestDbRestrictions.getCuurentPage();
		Integer perPage = taskRequestDbRestrictions.getRowsPerPage();
		return (List<AppMetaData>)getListByCriteria(criteria, (currentPage-1)*perPage, perPage);
	}
	/**
	 * @param userInformationVO
	 * @param taskRequestDbRestrictions
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<AppMetaData> getAllApplications(UserInformationVO userInformationVO, TaskRequestDbRestrictions taskRequestDbRestrictions){
		DetachedCriteria criteria = getAllAppRequestsCriteria(userInformationVO, taskRequestDbRestrictions);
		return (List<AppMetaData>)getListByCriteria(criteria);
	}
	
	/**
	 * @param userInformationVO
	 * @param taskRequestDbRestrictions
	 * @return
	 */
	private DetachedCriteria getAllAppRequestsCriteria(UserInformationVO userInformationVO, TaskRequestDbRestrictions taskRequestDbRestrictions){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.createAlias(AppMetaData.__SERVICE_MASTER, __SERVICE_MASTER_ALIAS, Criteria.INNER_JOIN);
		criteria.setFetchMode(__SERVICE_MASTER_ALIAS, FetchMode.JOIN);
		criteria.createAlias(AppMetaData.__APPLICATION_STATUS, __APPLICATION_STATUS_ALIAS, Criteria.INNER_JOIN);
		criteria.setFetchMode(__APPLICATION_STATUS_ALIAS, FetchMode.JOIN);
		
		//add date restriction
		Date dateFrom = null;
		Date dateTo = null;
		if(taskRequestDbRestrictions.getDateFrom() != null ) 
			dateFrom = UtilCommon.getDateFromStringFormat(taskRequestDbRestrictions.getDateFrom()) ;
		if(taskRequestDbRestrictions.getDateTo() != null )
			dateTo = UtilCommon.getDateFromStringFormat(taskRequestDbRestrictions.getDateTo());
		
		if(dateFrom!=null){
			criteria.add(Restrictions.ge(AppMetaData.__APPLICATION_DATE, dateFrom));
		} 
		if(dateTo!=null){
			criteria.add(Restrictions.le(AppMetaData.__APPLICATION_DATE, dateTo));
		}
		//add appId restriction	
		String refNo = taskRequestDbRestrictions.getRefNo();
		if(refNo!=null && !refNo.isEmpty()){
			criteria.add(Restrictions.eq(AppMetaData.__CLIENT_REF_NO, refNo));
		}
		//add service restriction
		Long serviceCode = taskRequestDbRestrictions.getServiceId();
		if(serviceCode!=null){
			criteria.add(Restrictions.eq(AppMetaData.__SERVICE_CODE, serviceCode));
		}
		
		Long statusId = taskRequestDbRestrictions.getStatusId();
		if(statusId!=null){
			criteria.add(Restrictions.eq(AppMetaData.__APPLICATION_STATUS_CODE, statusId));
		}
		//add client restriction
		if(userInformationVO.getClientId()!=null){
			criteria.add(Restrictions.eq(AppMetaData.__CLIENT_ID, userInformationVO.getClientId()));
		}
		criteria.add(Restrictions.or(Restrictions.ne(AppMetaData.__APP_COMMENTS, "OFFLINE"), Restrictions.isNull(AppMetaData.__APP_COMMENTS)));
		criteria.addOrder(Order.desc(AppMetaData.__APPLICATION_DATE));
		criteria.addOrder(Order.desc(AppMetaData.__ID));
		return criteria;
	}
	/**
	 * @param userInformationVO
	 * @param taskRequestDbRestrictions
	 * @return
	 */
	public Integer getApplicationCount(UserInformationVO userInformationVO, TaskRequestDbRestrictions taskRequestDbRestrictions){
		return getTotal(getAllAppRequestsCriteria(userInformationVO, taskRequestDbRestrictions));
	}
	
	@SuppressWarnings("unchecked")
	public List<AppMetaData> getAppMetaDataByPassportDetails(String passportName, String passportNo){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.createAlias(AppMetaData.__EMPLOYEE_DETAILS, __EMPLOYE_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__EMPLOYE_DETAIL_ALIAS, FetchMode.JOIN);
		criteria.createAlias(__PASSPORT_DETAILS_ASSOTH_PATH, __PASSPORT_DETAIL_ALIAS, Criteria.LEFT_JOIN);
		criteria.setFetchMode(__PASSPORT_DETAIL_ALIAS, FetchMode.JOIN);
		
		Disjunction disjunction = Restrictions.disjunction();
		disjunction.add(Restrictions.eq(__PASSPORT_DETAIL_ALIAS_PASSPORT_NAME, passportName).ignoreCase());
		disjunction.add(Restrictions.eq(__EMPLOYEE_DETAIL_ALIAS_PASSPORT_NAME, passportName).ignoreCase());
		
		criteria.add(disjunction);
		criteria.add(Restrictions.eq(__PASSPORT_DETAIL_ALIAS_PASSPORT_NO, passportNo).ignoreCase());
		return (List<AppMetaData>)getListByCriteria(criteria);
	}
	
	public Integer getRequestAmountByPersonId(String empId){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		joinEmploymentVisaCancelationInfo(criteria);
		joinEmploymentVisaRenewalInfo(criteria);
		joinVisitVisaCancelInfo(criteria);
		joinNocLettersInfo(criteria);
		
		Disjunction disjunction = Restrictions.disjunction();
		disjunction.add(Restrictions.eq(__EMPLOYMENT_VISA_CANCELATION_DETAILS_EMP_ID, empId));
		disjunction.add(Restrictions.eq(__EMPLOYMENT_VISA_RENEWAL_DETAILS_EMP_ID, empId));
		disjunction.add(Restrictions.eq(__VISIT_VISA_CANCELATION_DETAILS_EMP_ID, empId));
		disjunction.add(Restrictions.eq(__NOC_LETTER_EMP_ID, empId));
		
		criteria.add(disjunction);
		
		criteria.add(Restrictions.in(AppMetaData.__APPLICATION_STATUS_CODE, ApplicationStatus.PENDING_STATUSES));
		
		return getTotal(criteria);
	}
	
	/* (non-Javadoc)
	 * @see ae.pcfc.cldos.online.common.clddb.dao.AppMetaDataDAO#getEmploymentVisaPendingRequests(java.lang.Long)
	 */
	public Integer getEmploymentVisaPendingRequests(Long clientId){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.add(Restrictions.eq(AppMetaData.__CLIENT_ID, clientId));
		criteria.add(Restrictions.eq(AppMetaData.__PROCESS_CODE, Process.PROCESS_EMPLOYMENT_VISA));
		criteria.add(Restrictions.eq(AppMetaData.__APPLICATION_STATUS_CODE, ApplicationStatus.STATUS_APPROVED_BY_ONLINE_ADMIN));
		return getTotal(criteria);
	}
	/**
	 * @param clientId
	 * @return
	 */
	public Integer getVisitVisaPendingRequests(Long clientId){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.add(Restrictions.eq(AppMetaData.__CLIENT_ID, clientId));
		criteria.add(Restrictions.eq(AppMetaData.__PROCESS_CODE, Process.PROCESS_VISIT_VISA));
		criteria.add(Restrictions.eq(AppMetaData.__APPLICATION_STATUS_CODE, ApplicationStatus.STATUS_APPROVED_BY_ONLINE_ADMIN));
		return getTotal(criteria);
	}
	/**
	 * @param clientId
	 * @return
	 */
	public Integer getPendingRequests(Long clientId){
		DetachedCriteria criteria = DetachedCriteria.forClass(AppMetaData.class);
		criteria.add(Restrictions.eq(AppMetaData.__CLIENT_ID, clientId));
		criteria.add(Restrictions.or((Restrictions.eq(AppMetaData.__PROCESS_CODE, Process.PROCESS_VISIT_VISA)),(Restrictions.eq(AppMetaData.__PROCESS_CODE, Process.PROCESS_EMPLOYMENT_VISA))));
		criteria.add(Restrictions.eq(AppMetaData.__APPLICATION_STATUS_CODE, ApplicationStatus.STATUS_APPROVED_BY_ONLINE_ADMIN));
		return getTotal(criteria);
	}
	
	public AppMetaData getApplicationByReferenceNumber(String clientRefNo){
		return findByProperty(AppMetaData.class, AppMetaData.__CLIENT_REF_NO, clientRefNo);
	}
	
}
